var express = require('express');
var router = express.Router();
var db = require('./mysql/mysql')
var mysql = require('mysql');
var connection = mysql.createConnection({
  useConnectionPooling: true,
  host     : 'localhost',
  port: 3306, // MySQL 默认端口为 3306
  user     : 'root',  //用户名
  password : 'woaini.1.2', //用户密码
  database : 'orderdata',  //数据库
  // useConnectionPooling: true
  // host: '192.168.1.194',
  // user: 'skl',
  // password: 'skl123',
  // database: 'orderdata'
});

connection.connect(() => {
  console.log('数据库已连接');
});

// 获取MySQL账号数据
router.get('/getUser', function (req, res, next) {
  connection.query('SELECT * FROM account', function (error, results, fields) {
    if (error) throw error;
    res.send(results)
  })
})


// 订单退款部分
// 查询数据，获取数据
router.get('/', function (req, res, next) {
  connection.query(`SELECT * FROM data`, function (error, results, fields) {
    if (error) throw error;
    res.send(results)
  })
});

// 小程序

// 修改数据
router.post('/changeData',function(req,res,next){
  //   console.log(req.body.tel,'修改');
  // console.log(`UPDATE data set orderState = 已支付 where id = ${req.body.tel}`);
    connection.query(`UPDATE data set orderState = '已支付' where userTel = ${req.body.tel}`, function (error, results, fields) {
      if (error) throw error;
  console.log(results,'数据');
      res.send(results)
    })
})
// 删除订单
router.post('/deleteData',function(req,res,next){
    console.log(req.body.tel,'删除');
  // console.log(`UPDATE data set orderState = 已支付 where id = ${req.body.tel}`);
    connection.query(`DELETE FROM data WHERE userTel = ${req.body.tel}`, function (error, results, fields) {
      if (error) throw error;
  console.log(results,'数据');
      res.send(results)
    })
})


// 封装插入数据
function add(params) {
  const sql = 'INSERT INTO data set ?'
  connection.query(sql, params, function (error, results, fields) {
    if (error) throw error;
  })
}
// 向MySQL插入数据
router.post('/add', function (req, res, next) {
  console.log(req.body.obj);
  add([req.body.obj])
})
// 把mysql数据删除
router.post('/del', function (req, res, next) {
  console.log(req.body.id);
  connection.query(`DELETE FROM data WHERE orderNumber = ${req.body.id}`, function (error, results, fields) {
    if (error) throw error;
  })
})

// 查看详情数据
router.post('/see', function (req, res, next) {
  let id = req.body.id;
  connection.query(`SELECT * FROM data WHERE orderNumber = ${id}`, function (error, results, fields) {
    if (error) throw error;
    res.send(results[0])
  })
})

//React 

// 获取mysql优秀讲师数据
router.get('/lecture', function (req, res, next) {
  const sql = 'SELECT * FROM reactteacherdata'
  connection.query(sql, function (err, rows, fields) {
    if (err) throw err;
    // console.log(rows);
    res.send(rows)
  })
})

// 获取优惠卷数据
router.get('/coupon', function (req, res, next) {
  let sql = 'SELECT * FROM youhui'
  db.query(sql, function (err, rows, fields) {
    if (err) throw err;
    res.send(rows)
  })
})
// 获取头条数据
router.get('/toutiao', function (Req, res, next) {
  let sql = 'SELECT * FROM leadnews'
  db.query(sql, function (err, rows, fields) {
    if (err) throw err;
    res.send(rows)
  })
})

// 搜索数据
router.post('/sousuo', function (req, res, next) {
  let arr = []
  let val = req.body.val
  let sql = 'SELECT * FROM reactteacherdata'
  db.query(sql, function (err, rows, fields) {
    if (err) throw err;
    rows.forEach((item) => {
      JSON.parse(item.teacherClassList).forEach((ite) => {
        if (ite.classTitle.indexOf(val) != -1) {
          arr.push(ite)
        }
      })
    })
    res.send(arr)
  })
})

// 修改数据库数据
router.post('/gaiYouhui', function (req, res, next) {
  console.log(req.body);
  let {
    id, youName, youhui, price, type
  } = req.body.data
  db.query(`UPDATE youhui set type = '${1}' WHERE id='${req.body.type}'`, function (err, rows, fields) {
    if (err) throw err;
    console.log(rows);
  })
  let sql = `INSERT INTO miao (id,promote,isChecked,title,useRange,useDoor,faceValue,expiryDate,pouponType,type) values('${id}','1','false','${youhui}','${youName}','满100-${price}','${price}','${new Date().toLocaleDateString()}','2','${type}')`
  db.query(sql, function (err, rows, fields) {
    if (err) throw err;
    console.log(rows);
    res.send('添加成功')
  })
})

// 往mysql添加评论数据
router.post('/addPinglun', function (req, res, next) {
  let data = req.body;
  db.query('SELECT * FROM leadnews', function (err, rows, fields) {
    if (err) throw err;
    let reults = rows.findIndex((item) => {
      return item.tid === data.cid;
    })
    if (reults !== -1) {
      let nwedata = JSON.parse(rows[reults].leadPing)
      nwedata.push(data.val)
      const newdata = nwedata.map((item) => {
        return JSON.stringify(item)
      })
      JSON.stringify(newdata)
      var sql = `UPDATE LEADNEWS set leadPing = '[${newdata}]' WHERE tid = '${rows[reults].tid}'`;
      db.query(sql, function (err, rows, fields) {
        if (err) throw err;
        db.query('SELECT * FROM leadnews', function (err, results, fields) {
          if (err) throw err;
          res.send(results[reults].leadPing)
        })
      })
    }
  })
})

// 向第一周订单数据添加
router.post('/addOrder', function (req, res, next) {
  // console.log(req.body);
  let { cid, teacher, userTel, classTitle, classPrice, orderFlag, paymentStatus, PaymentTime } = req.body;
  let sql = `INSERT INTO data (orderNumber,userName,userTel,className,orderPrice,orderFlag,orderState,submitTime) values('${cid}','${teacher}','${userTel}','${classTitle}','${classPrice}','${orderFlag}','${paymentStatus === true ? '已支付' : '未支付'}','${PaymentTime}') `
  db.query(sql, function (err, rows, fiedls) {
    if (err) throw err;
    console.log(rows);
  })
  res.send('添加成功')
})


// 小程序上拉加载
router.get('/getTeacherClassList', function (req, res, next) {
  let { start, count } = req.query;
  let arr = [];
  const sql = `SELECT * FROM reactteacherdata`
  db.query(sql, function (err, rows, fiedls) {
    if (err) throw err;
    // console.log(rows);
    rows.map((item) => {
      item.teacherClassList = JSON.parse(item.teacherClassList)
    })
    rows.forEach((item) => {
      arr.push(...item.teacherClassList)
    })
    count = Number(start) + Number(count)
    res.send({ row: arr.slice(start, count), len: arr.length })
    // res.send(arr)
  })
})
module.exports = router;
